/**
* TS Client (http://www.transparent.co.nz)
* Copyright (c) 2004 Transparent Systems Limited
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the /doc/LICENSE.txt
* This is the GNU General Public License Version 2 as published by the Free Software Foundation.
* You can download this program from <a href="http://sourceforge.com/projects/ts-client">http://sourceforge.com/projects/ts-client</a>
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License Version 2 for more details.
*
* You should have received a copy of the GNU General Public License
* Version 2 along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
*
*/
/*
* Created on Nov 15, 2003
*
*/
package nz.co.transparent.client.controller;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import nz.co.transparent.client.db.ControllerException;
import nz.co.transparent.client.db.FinderException;
import nz.co.transparent.client.db.UpdaterException;
/**
* Handles generic function for a table:
* - query list of records
* - query specific reord
* - add record
* - update record
* - delete record
*
* Use this class to chain several methods into a transaction
* In case of 1 method only, use class GenericController in stead as it will handle the transaction.
*
* Client is responsible for:
* - commit or rollback of transaction
* - closing connection
*
* A final attempt will be made to close connection in finalize() method, but client must not rely on this !
*
* @author John Zoetebier
*
*/
public class GenericTransactionController {
private Logger log = Logger.getLogger("nz.co.transparent.client.db");
private Connection conn;
/**
*
* @param connection SQL connection
* @throws SQLException : Thrown if setAutoCommit fails
*/
public GenericTransactionController(Connection connection)
throws SQLException {
this.conn = connection;
this.conn.setAutoCommit(false); // Must run in transaction
}
/**
* Find all records of selected table with order clause
*
* @param tableName Name of table
* @param orderClause Name of column to order result list.
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAll(String tableName, String orderClause)
throws ControllerException {
return findAllWhere(tableName, orderClause, null);
}
/**
* Find all records of selected table in random order
*
* @param tableName Name of table
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAll(String tableName)
throws ControllerException {
return findAll(tableName, null);
}
/**
* Find all records of selected table with order clause and where clause
*
* @param tableName Name of table
* @param orderClause Name of column to order result list.
* @param whereClause Where clause
* @return List of <code>Map</code> entries
* @throws ControllerException
*/
public List findAllWhere(String tableName, String orderClause, String whereClause)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler rsh = new MapListHandler();
String sql = null;
try {
sql = "SELECT * FROM " + tableName;
if (whereClause != null) {
sql += " WHERE " + whereClause;
}
if (orderClause != null) {
sql += " ORDER BY " + orderClause;
}
return (List) queryRunner.query(conn, sql, rsh);
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Find a single record in table with order and where clause
*
* @param tableName Table name
* @param whereClause Where clause. Pass null to omit clause.
* @return Map with {ColumnName, ColumnValue}
* @throws ControllerException Any exception is re-thrown as a ControllerException
* @throws FinderException If there are no results a FinderException is thrown
*/
public Map findWhere(String tableName, String whereClause)
throws ControllerException, FinderException {
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler rsh = new MapListHandler();
String sql = null;
try {
sql = "SELECT * FROM " + tableName;
if (whereClause != null) {
sql += " WHERE " + whereClause;
}
List mapList = (List) queryRunner.query(conn, sql, rsh);
if (mapList.size() == 0l) {
throw new FinderException();
}
Iterator iterator = mapList.iterator();
if (iterator.hasNext()) {
return (Map) iterator.next();
} else {
return null;
}
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Add record to table.
*
* @param columnMap
* @param tableName
* @param primaryKeyName Primary key column name.
* Pass null value in columnMap to have primary key generated.
* A non-null value in columnMap for primary key is used as is.
* @throws ControllerException
*/
public int insertRecord(String tableName, String primaryKeyName, Map columnMap)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner();
String sql = null;
String columnName = null;
int i;
try {
// Get primary key in case primary key is null
if (columnMap.get(primaryKeyName) == null) {
//int uniqueKey = nz.co.transparent.client.db.SQL.getUniqueKey(conn, tableName, primaryKeyName);
int uniqueKey = nz.co.transparent.client.db.SQL.getUniqueKey(tableName, primaryKeyName);
columnMap.put(primaryKeyName, new Integer(uniqueKey));
}
sql = "insert into " + tableName;
String parm = null;
Set columnSet = columnMap.keySet();
Iterator iterator = columnSet.iterator();
Object[] params = new Object[columnSet.size()];
i = 0;
int j = 0;
// Use prepared statement to avoid escaping special character
while (iterator.hasNext()) {
columnName = (String) iterator.next();
// CURRENT_TIMESTAMP must be set directly into SQL to force date created by server
// Alternatively these columns can be left out of the map
if (columnName.equals("date_created")) {
parm = "CURRENT_TIMESTAMP";
} else if (columnName.equals("date_updated")) {
parm = "CURRENT_TIMESTAMP";
} else {
parm = "?";
params[i++] = columnMap.get(columnName);
}
if (j++ == 0) {
sql += " set " + columnName + "=" + parm;
} else {
sql += " ," + columnName + "=" + parm;
}
}
try {
return queryRunner.update(conn, sql, params);
} catch (SQLException se) {
throw new ControllerException(se);
}
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Delete a record
*
* @param tableName Name of table
* @param whereClause Where clause
* @throws ControllerException
*/
public int deleteRecord(String tableName, String whereClause)
throws ControllerException {
QueryRunner queryRunner = new QueryRunner();
String sql = null;
try {
sql = "delete from " + tableName;
if (whereClause != null) {
sql += " where " + whereClause;
}
return queryRunner.update(conn, sql);
} catch (SQLException se) {
log.warning("GenericController SQLException: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Delete a record
*
* @param tableName Name of table
* @throws ControllerException
*/
public int deleteRecord(String tableName)
throws ControllerException {
return deleteRecord(tableName, null);
}
/**
* Update a record
*
* @param tableName Name of table
* @param primaryKeyName Name of primary key
* @param columnMap Map with {ColumnName, ColumnValue}
* @throws ControllerException
* @throws UpdaterException Thrown if concurrent change has happened
*/
public int updateRecord(String tableName, String primaryKeyName, Map columnMap)
throws ControllerException, UpdaterException {
QueryRunner queryRunner = new QueryRunner();
ResultSetHandler rsh = new MapHandler();
String sql = null;
try {
sql = "select * from " + tableName;
sql += " where (" + primaryKeyName + "=?)";
Map columnMapTemp = (Map) queryRunner.query(conn, sql, columnMap.get(primaryKeyName), rsh);
if (columnMapTemp == null) {
throw new ControllerException("Cannot find record.");
}
Date oldDate = (java.util.Date) columnMap.get("date_updated");
Date newDate = (java.util.Date) columnMapTemp.get("date_updated");
if (!oldDate.equals(newDate)) {
throw new UpdaterException(); // Signal that record has already been changed
}
sql = "update " + tableName;
String parameter = null;
String columnName = null;
List columnNameList = null;
List paramList = new ArrayList();
// Iterate over columns
Set columnSet = columnMapTemp.keySet();
Iterator iterator = columnSet.iterator();
int i = 0;
while (iterator.hasNext()) {
columnName = (String) iterator.next();
if (columnName.equals("date_updated")) {
parameter = "CURRENT_TIMESTAMP";
} else {
parameter = "?";
paramList.add(columnMap.get(columnName));
}
if (i++ == 0) {
sql += " set " + columnName + " = " + parameter;
} else {
sql += " ," + columnName + " = " + parameter;
}
}
sql += " where (" + primaryKeyName + "=?)";
paramList.add(columnMap.get(primaryKeyName)); // Add primaryKey value to paramList
return queryRunner.update(conn, sql, paramList.toArray());
} catch (SQLException se) {
log.warning("SQL Exception: " + se.getMessage());
throw new ControllerException(se);
}
}
/**
* Close any resource still open
*/
public void finalize() {
try {
DbUtils.close(conn);
} catch (SQLException se) {
log.warning("GenericTransactionHandler::finalize() : " + se.getMessage());
}
}
}